# Process RAW PUBLIC SIGNALS DATA 
rm(list=ls())

(WD <- getwd())
if (!is.null(WD)) setwd(WD)

library(haven)
library(AER)
library(sandwich)
library(lmtest)
library(pracma)
library(stargazer)
library(plm)
library(pracma)
library(DataCombine)
library(jtools)
library(plyr)
library(readxl)
library(foreign)

lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

##### NEER DATA #############
data    = read_excel("neer_bis.xlsx", sheet = "Nominal")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$neer    = as.numeric(data$NNUS) 
data$dlneer  = 100*(log(data$neer)-log(lagpad(data$neer,4)))
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1964+(tmp-1)/4

data         = subset(data, select = c(qdate,dlneer))

save(data,file="raw_data_neer.rda")
clear()

##### TIPS DATA #############
data  = read_excel("tips-spread-fred.xls", sheet = "Data")
  
data$tips    = as.numeric(data$T10YIE)
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 2003+(tmp-1)/4

data         = subset(data, select = c(qdate,tips))

save(data,file="raw_data_tips.rda")
clear()

##### IMPORT PRICE DATA #############
data  = read_excel("import-prices-fred.xls", sheet = "Data")
lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

data$imp     = as.numeric(data$IMP) 
data$dlimp   = 100*(log(data$imp)-log(lagpad(data$imp,4)))
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1982.75+(tmp-1)/4

data         = subset(data, select = c(qdate,dlimp))

save(data,file="raw_data_imp.rda")
clear()

##### OIL PRICE DATA #############
data  = read_excel("wti-fred.xls", sheet = "Data")
lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

data$oil     = as.numeric(data$WTISPLC) 
data$dloil   = 100*(log(data$oil)-log(lagpad(data$oil,4)))
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1946 +(tmp-1)/4

data         = subset(data, select = c(qdate,dloil))

save(data,file="raw_data_oil.rda")
clear()

##### UNEMPlOYMENT RATE DATA #############
data  = read_excel("u-rate-fred.xls", sheet = "Data")

data$urate   = as.numeric(data$UNRATE)
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1948 +(tmp-1)/4

data         = subset(data, select = c(qdate,urate))

save(data,file="raw_data_unemployment.rda")
clear()

##### CLEVELAND FED FIN INFL EXP #############
data  = read_excel("cleveland-fed-inflation.xls", sheet = "Expected Inflation")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]

data$cf_exp_infl   = as.numeric(data$`1 year Expected Inflation`) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1982 +(tmp-1)/4

data         = subset(data, select = c(qdate,cf_exp_infl))

save(data,file="raw_data_cf_fed.rda")
clear()

##### TERM SPREAD #############
data  = read_excel("term-spread-fred.xls", sheet = "Data")

data$term    = as.numeric(data$T10Y3M) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1982 +(tmp-1)/4

data         = subset(data, select = c(qdate,term))

save(data,file="raw_data_termspread.rda")
clear()

##### STOCKS #############
data  = read_excel("sp500-shiller.xls", sheet = "Data_SP")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]

lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

data$stocks   = as.numeric(data$SP) 
tmp           = row(data)
tmp           = tmp[,1]
data$qdate    = 1960 +(tmp-1)/4
dev.lm        = lm(log(stocks) ~ qdate, data=data) 
dev.res       = resid(dev.lm)
data$dlstocks = dev.res

data         = subset(data, select = c(qdate,dlstocks))

save(data,file="raw_data_stocks.rda")
clear()

##### MERGE #############
load("raw_data_neer.rda")
data_new    = data
iter        = 0
data_series =  c('raw_data_cf_fed.rda','raw_data_imp.rda','raw_data_oil.rda','raw_data_stocks.rda','raw_data_termspread.rda', 'raw_data_tips.rda', 'raw_data_unemployment.rda')
for ( i in data_series){
  iter     = iter + 1
  load(i)
#  if (iter <= 2 & iter >=4){
#    data   = data[-4:-1,]
#  } 
  data_new = merge(data_new, data, all=TRUE)
}

data = data_new[data_new$qdate>=1970,]
save(data,file="public_signals_hard.rda")
clear()
